# -*- coding: utf-8 -*-

import xlrd
import xlwt

a_excel = xlrd.open_workbook("a.xlsx")
b_excel = xlrd.open_workbook("b.xlsx")

c_excel_path = "c.xls"
pk_name = "LocationID"
rp_names = {"TW-DMeta-bid", "TW-Mobile-bid", "HK-Mobile-bid", "HK-Dmeta-bid", "BMP"}

c_excel = xlwt.Workbook(encoding='utf8')

a_sheet = a_excel.sheets()[0]
b_sheet = b_excel.sheets()[0]

a_header = dict()
b_header = dict()

a_colNames = a_sheet.row_values(0)
b_colNames = b_sheet.row_values(0)

for i in range(len(a_colNames)):
    a_header[a_colNames[i]] = i

for i in range(len(b_colNames)):
    b_header[b_colNames[i]] = i

def get_table(sheet, header):
    a_table = {}
    for i in range(1, sheet.nrows):
        rowdic = {}
        pk = 0
        for k in header:
            j = header[k]
            cv = sheet.cell(i, j).value
            if k == pk_name:
                pk = int(cv)
            rowdic[str(j)] = str(cv)

        if pk > 0:
            a_table[pk] = rowdic
    return a_table

def get_cell_val(k, row):
    if row == None: return ""
    for ik in rp_names:
        if ik == k:
            i = b_header.get(ik)
            return row.get(str(i))
    return ""

a_t = get_table(a_sheet, a_header)
b_t = get_table(b_sheet, b_header)

c_sheet = c_excel.add_sheet("newsheet")
for k in a_header:
    i = a_header[k]
    c_sheet.write(0, i, k)

ri = 0
for k in a_t:
    row = a_t[k]
    ri = ri + 1
    f_row = b_t.get(k) 
    for ik in a_header:
        j = a_header[ik]
        v = row[str(j)]
        tv = get_cell_val(ik, f_row)
        if not tv == "":
            v = tv
        c_sheet.write(ri, j, v)
c_excel.save(c_excel_path)
